package cn.btks.resultintegration.achievements.service.impl;

import cn.btks.commonserver.pojo.PageList;
import cn.btks.commonserver.result.Constants;
import cn.btks.commonserver.result.ResultState;
import cn.btks.commonserver.result.StateInfo;
import cn.btks.commonserver.util.FileUtil;
import cn.btks.resultintegration.achievements.pojo.EnvironmentAttribute;
import cn.btks.resultintegration.achievements.pojo.TextAchievements;
import cn.btks.resultintegration.achievements.repository.TextAchievementsRepository;
import cn.btks.resultintegration.achievements.service.TextAchievementsService;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import javax.servlet.http.HttpServletRequest;
import javax.xml.soap.Text;
import java.io.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @Author lijun
 * @Date 2021/1/6 12:10
 */
@Service
@Transactional
public class TextAchievementsServiceImpl implements TextAchievementsService {

    @Autowired
    TextAchievementsRepository textAchievementsRepository;

    @Override
    public TextAchievements findByUnitCode(String unitCode) {
        return textAchievementsRepository.findByUnitCode(unitCode);
    }

    @Override
    public ResultState findPage(Integer pageNumber, Integer pageSize, String code, String city, String county) {
        PageRequest pageRequest = PageRequest.of(pageNumber - 1, pageSize);
        Specification<TextAchievements> specification = new Specification<TextAchievements>() {
            @Override
            public Predicate toPredicate(Root<TextAchievements> root,
                                         CriteriaQuery<?> criteriaQuery,
                                         CriteriaBuilder criteriaBuilder) {
                List<Predicate> predicates = new ArrayList<>();
                if (!StringUtils.isEmpty(code)) {
                    predicates.add(criteriaBuilder.like(root.get("unitCode"),
                            "%" + code + "%"));
                }
                if (!StringUtils.isEmpty(city)) {
                    predicates.add(criteriaBuilder.like(root.get("division"),
                            "%" + city + "%"));
                }
                if (!StringUtils.isEmpty(county)) {
                    predicates.add(criteriaBuilder.like(root.get("regiment"),
                            "%" + county + "%"));
                }
                Predicate[] predicates1 = new Predicate[predicates.size()];
                return criteriaBuilder.and(predicates.toArray(predicates1));
            }
        };
        Page<TextAchievements> configPage = textAchievementsRepository.findAll(specification, pageRequest);
        if(configPage.getTotalElements() > 0){
            PageList pageList = new PageList();
            pageList.setResult(configPage.getContent());
            pageList.setPageNum(configPage.getTotalPages());
            pageList.setTotalCount(configPage.getTotalElements());
            return new ResultState(StateInfo.FIND_OK.getCode(), StateInfo.FIND_OK.getMsg(), pageList);
        }
        return new ResultState(StateInfo.FIND_NULL.getCode(), StateInfo.FIND_NULL.getMsg());
    }

    @Override
    public ResultState save(TextAchievements textAchievements, MultipartFile multipartFile, HttpServletRequest request) {
        if(textAchievements != null){
            TextAchievements textAchievements1 = textAchievementsRepository.save(textAchievements);
            return textAchievements1 != null ? new ResultState(StateInfo.SAVE_OK.getCode(), StateInfo.SAVE_OK.getMsg(), textAchievements1) :
                    new ResultState(StateInfo.SAVE_ERROR.getCode(), StateInfo.SAVE_ERROR.getMsg());
        }
        return new ResultState(StateInfo.SAVE_ERROR.getCode(), StateInfo.SAVE_ERROR.getMsg());
    }

    @Override
    public ResultState saveAll(List<TextAchievements> achievements) {
        List<TextAchievements> textAchievementsList = textAchievementsRepository.saveAll(achievements);
        return textAchievementsList != null ? new ResultState(StateInfo.SAVE_OK.getCode(), StateInfo.SAVE_OK.getMsg(), textAchievementsList) :
                new ResultState(StateInfo.SAVE_ERROR.getCode(), StateInfo.SAVE_ERROR.getMsg());
    }

    @Override
    public ResultState upload(MultipartFile multipartFile, HttpServletRequest request) throws IOException {
        if(multipartFile != null){
            String fjName = UUID.randomUUID() + "_" + multipartFile.getOriginalFilename();
            String pathname = request.getSession(true).getServletContext().getRealPath("//") + Constants.ACHIEVEMENTS_TEXT_FILE + fjName;
            List<TextAchievements> textAchievementsList = new ArrayList<>();
            if (!FileUtil.fileUpload(multipartFile, pathname)) {
                File file = new File(pathname);
                String filePath = "";
                if(file.exists()){
                    filePath = file.getAbsolutePath();
                }
                //Excel工具项
                InputStream inputStream = new FileInputStream(filePath);
                XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
                XSSFSheet sheet = workbook.getSheetAt(0);
                for(Row row : sheet){
                    if(row.getRowNum() == 0){
                        continue;
                    }
                    TextAchievements textAchievements = new TextAchievements();
                    //设置单元格类型
                    row.getCell(0).setCellType(CellType.STRING);
                    //序号
                    String serialNum = row.getCell(0).getStringCellValue();
                    //环境管控单元编码
                    String unitCode = row.getCell(1).getStringCellValue();
                    unitCode = unitCode.trim();
                    //单元名称
                    String unitName = row.getCell(2).getStringCellValue();
                    //省
                    String province = row.getCell(3).getStringCellValue();
                    //师
                    String division = row.getCell(4).getStringCellValue();
                    //团
                    String regiment = row.getCell(5).getStringCellValue();
                    //管控单元分类
                    String unitClassify = row.getCell(6).getStringCellValue();
                    //空间布局约束
                    String spaceRestrain = row.getCell(7).getStringCellValue();
                    //污染物排放管控
                    String pollutantRestrain = row.getCell(8).getStringCellValue();
                    //环境风险防控
                    String environmentRestrain = row.getCell(9).getStringCellValue();
                    //资源利用效率
                    String resourceUtilization = row.getCell(10).getStringCellValue();

                    textAchievements.setUnitCode(unitCode);
                    textAchievements.setUnitName(unitName);
                    textAchievements.setProvince(province);
                    textAchievements.setDivision(division);
                    textAchievements.setRegiment(regiment);
                    textAchievements.setUnitClassify(unitClassify);
                    textAchievements.setSpaceRestrain(spaceRestrain);
                    textAchievements.setPollutantRestrain(pollutantRestrain);
                    textAchievements.setEnvironmentRestrain(environmentRestrain);
                    textAchievements.setResourceUtilization(resourceUtilization);
                    textAchievementsList.add(textAchievements);
                }
            }
            if(textAchievementsList.size() > 0){
                List<TextAchievements> textAchievementsList1 = textAchievementsRepository.saveAll(textAchievementsList);
                return textAchievementsList1 != null && textAchievementsList1.size() > 0 ? new ResultState(StateInfo.Upload_File_OK.getCode(), StateInfo.Upload_File_OK.getMsg(), textAchievementsList1) :
                        new ResultState(StateInfo.Upload_File_ERROR.getCode(), StateInfo.Upload_File_ERROR.getMsg());
            }
        }
        return new ResultState(StateInfo.Upload_File_ERROR.getCode(), StateInfo.Upload_File_ERROR.getMsg());
    }

    @Override
    public ResultState update(TextAchievements textAchievements) {
        if(textAchievements.getId() != null){
            Optional<TextAchievements> optional = textAchievementsRepository.findById(textAchievements.getId());
            if(optional.isPresent()){
                TextAchievements textAchievements1 = textAchievementsRepository.save(textAchievements);
                return textAchievements1 != null ? new ResultState(StateInfo.SAVE_OK.getCode(), StateInfo.SAVE_OK.getMsg(), textAchievements1) :
                        new ResultState(StateInfo.SAVE_ERROR.getCode(), StateInfo.SAVE_ERROR.getMsg());
            }
        }
        return new ResultState(StateInfo.SAVE_ERROR.getCode(), StateInfo.SAVE_ERROR.getMsg());
    }

    @Override
    public ResultState delete(Long id) {
        Optional<TextAchievements> optional = textAchievementsRepository.findById(id);
        if(optional.isPresent()){
            int result = textAchievementsRepository.delete(id);
            return result > 0 ? new ResultState(StateInfo.DELETE_OK.getCode(), StateInfo.DELETE_OK.getMsg(), result) :
                    new ResultState(StateInfo.DELETE_ERROR.getCode(), StateInfo.DELETE_ERROR.getMsg());
        }
        return  new ResultState(StateInfo.DELETE_ERROR_2.getCode(), StateInfo.DELETE_ERROR_2.getMsg());
    }

    @Override
    public ResultState export(String code, String cityName, HttpServletRequest request) throws IOException {
        Specification<TextAchievements> specification = new Specification<TextAchievements>() {
            @Override
            public Predicate toPredicate(Root<TextAchievements> root,
                                         CriteriaQuery<?> criteriaQuery,
                                         CriteriaBuilder criteriaBuilder) {
                List<Predicate> predicates = new ArrayList<>();
                if (!StringUtils.isEmpty(code)) {
                    predicates.add(criteriaBuilder.like(root.get("unitCode"),
                            "%" + code + "%"));
                }
                if (!StringUtils.isEmpty(cityName)) {
                    if(!cityName.equals("新疆生产建设兵团")){
                        predicates.add(criteriaBuilder.like(root.get("division"),
                                "%" + cityName + "%"));
                    }
                }
                Predicate[] predicates1 = new Predicate[predicates.size()];
                return criteriaBuilder.and(predicates.toArray(predicates1));
            }
        };
        List<TextAchievements> textAchievementsList = textAchievementsRepository.findAll(specification);
        String filePath = export(request,textAchievementsList, "环境管控单元成果清单");
        return !StringUtils.isEmpty(filePath) ? new ResultState(StateInfo.DOWNLOAD_OK.getCode(), StateInfo.DOWNLOAD_OK.getMsg(), filePath) :
                new ResultState(StateInfo.DOWNLOAD_ERROR.getCode(), StateInfo.DOWNLOAD_ERROR.getMsg());
    }

    /**
     * 导出excel方法
     * @return
     */
    private String export(HttpServletRequest request, List<TextAchievements> textAchievementsList, String fileName) throws IOException {
        //导出文件所在文件夹路径
        String srcFilePathDir = request.getSession(true).getServletContext().getRealPath("//") + Constants.ACHIEVEMENTS_EXPORT_FILE;
        //生成文件路径
        String srcFinalPath = request.getSession(true).getServletContext().getRealPath("//") + Constants.ACHIEVEMENTS_EXPORT_FILE +fileName + ".xlsx";
        //返回路径
        String srcFinalPathResult =  Constants.ACHIEVEMENTS_EXPORT_FILE + fileName + ".xlsx";
        //模板文件
        String srcTemplateFile = "/res/achievements/export/template/" + "GKQD.xlsx";
        System.out.println(srcFinalPath);
        File dir1 = new File(srcFilePathDir);
        if (!dir1.exists()) {
            dir1.mkdirs();
        }
        File file1 = new File(srcFinalPath);
        if(file1.exists()){
            file1.delete();
        }

        //Excel工具项
        InputStream inputStream = this.getClass().getResourceAsStream(srcTemplateFile);
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        XSSFSheet sheet = workbook.getSheetAt(0);
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFillBackgroundColor(IndexedColors.WHITE.getIndex());
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setBorderBottom(BorderStyle.NONE);
        cellStyle.setBorderTop(BorderStyle.NONE);
        cellStyle.setBorderLeft(BorderStyle.NONE);
        cellStyle.setBorderRight(BorderStyle.NONE);

        int rowStartIndexTwo = 1;//模板数据起始行索引（行列索引均从0开始）

        if (textAchievementsList != null && textAchievementsList.size() > 0) {
            int serialNum = 1;
            for (TextAchievements item : textAchievementsList) {
                int columnIndex = 0;
                Row row = sheet.createRow(rowStartIndexTwo);
                row.setHeightInPoints(24);
                //序号
                CreateCell(cellStyle, row, columnIndex++, String.valueOf(serialNum), sheet);
                //环境管控单元编码
                CreateCell(cellStyle, row, columnIndex++, item.getUnitCode(), sheet);
                //环境管控单元名称
                CreateCell(cellStyle, row, columnIndex++, item.getUnitName(), sheet);
                //市级行政单元
                CreateCell(cellStyle, row, columnIndex++, item.getDivision(), sheet);
                //县级行政单元
                CreateCell(cellStyle, row, columnIndex++, item.getRegiment(), sheet);
                //环境管控单元类型
                CreateCell(cellStyle, row, columnIndex++, item.getUnitClassify(), sheet);
                //空间布局约束
                CreateCell(cellStyle, row, columnIndex++, item.getSpaceRestrain(), sheet);
                //污染物排放管控
                CreateCell(cellStyle, row, columnIndex++, item.getPollutantRestrain(), sheet);
                //环境风险防控
                CreateCell(cellStyle, row, columnIndex++, item.getEnvironmentRestrain(), sheet);
                //资源开发效率
                CreateCell(cellStyle, row, columnIndex++, item.getResourceUtilization(), sheet);

                serialNum++;
                rowStartIndexTwo++;
            }
        }
        //本地测试导出测试
        File file = new File(srcFinalPath);
        if (!file.exists()) {
            file.createNewFile();
            System.out.println(file.getPath());
        }
        OutputStream outputStream = new FileOutputStream(file);
        workbook.write(outputStream);
        outputStream.close();
        inputStream.close();

        return srcFinalPathResult;
    }

    private void RegionStyle(CellRangeAddress cellRangeAddress, XSSFSheet sheet) {
        RegionUtil.setBorderBottom(BorderStyle.NONE, cellRangeAddress, sheet); // 下边框
        RegionUtil.setBorderLeft(BorderStyle.NONE, cellRangeAddress, sheet); // 左边框
        RegionUtil.setBorderRight(BorderStyle.NONE, cellRangeAddress, sheet); // 有边框
        RegionUtil.setBorderTop(BorderStyle.NONE, cellRangeAddress, sheet); // 上边框
    }

    private void CreateCell(CellStyle cellStyle, Row row, int cellIndex) {

        Cell cell = row.createCell(cellIndex);
        cell.setCellStyle(cellStyle);
    }

    private void CreateCell(CellStyle cellStyle, Row row, int cellIndex, String cellValue, XSSFSheet sheet) {
        Cell cell = row.createCell(cellIndex);
        CellRangeAddress cellRangeAddress = new CellRangeAddress(row.getRowNum(), row.getRowNum(), cellIndex,
                cellIndex);
        RegionUtil.setBorderBottom(BorderStyle.NONE, cellRangeAddress, sheet); // 下边框
        RegionUtil.setBorderLeft(BorderStyle.NONE, cellRangeAddress, sheet); // 左边框
        RegionUtil.setBorderRight(BorderStyle.NONE, cellRangeAddress, sheet); // 有边框
        RegionUtil.setBorderTop(BorderStyle.NONE, cellRangeAddress, sheet); // 上边框
        cell.setCellStyle(cellStyle);
        cell.setCellValue(cellValue);
    }

    private void CreateCell(CellStyle cellStyle, Row row, int cellIndex, String cellValue) {

        Cell cell = row.createCell(cellIndex);
        cell.setCellStyle(cellStyle);
        cell.setCellValue(cellValue);
    }

    private void SetCellValue(CellStyle cellStyle, Cell cell, String value) {
        cell.setCellValue(value);
        cell.setCellStyle(cellStyle);
    }

    private void SetRegionCellValue(Map<Integer, Object> map, XSSFSheet sheet) {
        Set<Integer> indexSet = map.keySet();
        for (Integer indexRow : indexSet) {
            Map<Integer, String> cellInfo = (Map<Integer, String>) map.get(indexRow);
            Set<Integer> cellIndex = cellInfo.keySet();
            for (Integer cellIndexItem : cellIndex) {
                sheet.getRow(indexRow).getCell(cellIndexItem).setCellValue(cellInfo.get(cellIndexItem));
            }
        }

    }

    private String dateToString(Date date) {
        if (date != null) {
            SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");//日期格式
            String result = format.format(date);
            return result;
        }
        return "";
    }

    private Date getYearFirst(Date date) throws ParseException {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        Calendar calendar = new GregorianCalendar();
        calendar.setTime(date);
        calendar.clear(Calendar.MONTH);
        calendar.add(Calendar.YEAR, 0);
        /*c.add(Calendar.MONTH, 0);*/
        calendar.set(Calendar.DAY_OF_MONTH, 1);
        //设置为1号,当前日期既为本月第一天
        //c.set(Calendar.DAY_OF_MONTH, 1);

        String first = format.format(calendar.getTime());
        return format.parse(first);
    }

    public static Date getYearLast(Date date) throws ParseException {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        Calendar calendar = new GregorianCalendar();
        calendar.setTime(date);
        calendar.clear(Calendar.MONTH);
        calendar.set(Calendar.MONTH, 11);
        calendar.set(Calendar.DAY_OF_MONTH,
                calendar.getActualMaximum(Calendar.DAY_OF_MONTH));
        String last = format.format(calendar.getTime());

        return format.parse(last);
    }

    public static Row insertRow(XSSFSheet sheet, int startRow) {
        Row row = null;
        if (sheet.getRow(startRow) != null) {
            int lastRowNo = sheet.getLastRowNum();
            sheet.shiftRows(startRow, lastRowNo, 1, true, false);
        }
        row = sheet.createRow(startRow);
        row.setHeightInPoints(24);
        return row;
    }
}
